package com.foreknow.demo11;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class DBUtil {
    Connection conn = null;
    PreparedStatement pstmt = null;
    ResultSet rs = null;
    /**
     * 连接数据库
     * @return
     * @throws ClassNotFoundException
     * @throws SQLException
     *
     * 事务：是一个工作单元（一系列操作insert delete update），这些操作要么全部成功，只要有一个失败，就会取消之前的所有操作
     * 事务的操作：提交事务  回滚事务
     *Connection中会包含事务操作的方法：commit（） rollback（） 注意：jdbc中的事务默认是自动提交的
     *
     *
     */
    public Connection getConnection() throws ClassNotFoundException, SQLException {
        //1. 加载驱动程序
        Class.forName("com.mysql.cj.jdbc.Driver");
        //2. 获取连接(Connection)
        conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/Fender?useSSL=false&allowPublicKeyRetrieval=true&serverTimezone=UTC","root","1234");
        //设置事务的提交模式为手动提交
        conn.setAutoCommit(false);
        return conn;
    }

    public ResultSet getAll(String sql,Object...values) throws SQLException {
        pstmt = conn.prepareStatement(sql);
        for (int i = 0;i<values.length;i++){
            pstmt.setObject(i+1,values[i]);
        }
        rs = pstmt.executeQuery();
        return rs;
    }

    public boolean update(String sql,Object...values) throws SQLException {
        pstmt = conn.prepareStatement(sql);
        for (int i = 0;i<values.length;i++){
            pstmt.setObject(i+1,values[i]);
        }
        int a = pstmt.executeUpdate();
        return a == 1;
    }

    public void closed() throws SQLException {
        if(rs!=null) {
            rs.close();
        }
        if(pstmt!=null) {
            pstmt.close();
        }
        if(conn!=null){
            conn.close();
        }
    }

    public static void main(String[] args) {
        DBUtil dbUtil = new DBUtil();
        try {
            Connection conn = dbUtil.getConnection();
            ResultSet rs = dbUtil.getAll("select * from admin where id =? " ,2);
            boolean isRight = dbUtil.update("insert into admin(username,password) values(?,?)","test","23232");
            //提交事务
            conn.commit();
            if (isRight){
                System.out.println("添加成功");
            }else {
                System.out.println("添加失败");
            }
            while (rs.next()){
                //获取到当前行的第几列
                int id = rs.getInt("id");
                String username = rs.getString("username");
                String password = rs.getString("password");
                Admin admin = new Admin();
                admin.setId(id);
                admin.setUsername(username);
                admin.setPassword(password);
                System.out.println(admin.getUsername());
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
    }
}
